from datetime import datetime

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from utils.sqlalchemy.medel import FlowReport

# 创建基础模型类
Base = declarative_base()


class DatabaseManager:
    def __init__(self, db_url):
        """
        初始化数据库管理器
        :param db_url: 数据库连接 URL
        """
        self.engine = create_engine(db_url)
        self.Session = sessionmaker(bind=self.engine)

    def create_tables(self):
        """
        创建所有定义的数据库表
        """
        Base.metadata.create_all(self.engine)

    def insert_data(self, model, data):
        """
        高阶插入函数，支持单条和批量插入
        :param model: 数据库表对应的 SQLAlchemy 模型类
        :param data: 要插入的数据，可以是单个字典或字典列表
        :return: 插入成功返回 True，失败返回 False
        """
        session = self.Session()
        try:
            if isinstance(data, dict):
                # 单条插入
                record = model(**data)
                session.add(record)
            elif isinstance(data, list):
                # 批量插入
                records = [model(**item) for item in data]
                session.add_all(records)
            else:
                raise ValueError("数据类型必须是字典或字典列表")

            session.commit()
            return True
        except Exception as e:
            session.rollback()
            print(f"插入数据时出错: {e}")
            return False
        finally:
            session.close()

    def query_data(self, model, filters=None, range_filters=None, like_filters=None, in_filters=None):
        """
        查询数据
        :param model: 数据库表对应的 SQLAlchemy 模型类
        :param filters: 查询条件，字典形式，键为字段名，值为字段值
        :param range_filters: 范围查询条件，字典形式，键为字段名，值为 (min_value, max_value) 元组
        :param like_filters: 模糊查询条件，字典形式，键为字段名，值为模糊匹配字符串
        :param in_filters: IN 查询条件，字典形式，键为字段名，值为包含多个值的列表
        :return: 查询结果列表
        """
        session = self.Session()
        try:
            query = session.query(model)
            if filters:
                for key, value in filters.items():
                    query = query.filter(getattr(model, key) == value)
            if range_filters:
                for key, (min_value, max_value) in range_filters.items():
                    query = query.filter(getattr(model, key) >= min_value, getattr(model, key) <= max_value)
            if like_filters:
                for key, value in like_filters.items():
                    query = query.filter(getattr(model, key).like(f"%{value}%"))
            if in_filters:
                for key, values in in_filters.items():
                    query = query.filter(getattr(model, key).in_(values))
            results = query.all()
            return results
        except Exception as e:
            print(f"查询数据时出错: {e}")
            return []
        finally:
            session.close()

    def update_data(self, model, update_data,filters=None, range_filters=None, like_filters=None, in_filters=None):
        """
        修改数据
        :param model: 数据库表对应的 SQLAlchemy 模型类
        :param filters: 查询条件，字典形式，键为字段名，值为字段值
        :param update_data: 要更新的数据，字典形式
        :return: 修改成功返回 True，失败返回 False
        """
        session = self.Session()
        try:
            query = session.query(model)
            if filters:
                for key, value in filters.items():
                    query = query.filter(getattr(model, key) == value)
            if range_filters:
                for key, (min_value, max_value) in range_filters.items():
                    query = query.filter(getattr(model, key) >= min_value, getattr(model, key) <= max_value)
            if like_filters:
                for key, value in like_filters.items():
                    query = query.filter(getattr(model, key).like(f"%{value}%"))
            if in_filters:
                for key, values in in_filters.items():
                    query = query.filter(getattr(model, key).in_(values))
            rows_affected = query.update(update_data)
            session.commit()
            return rows_affected > 0
        except Exception as e:
            session.rollback()
            print(f"修改数据时出错: {e}")
            return False
        finally:
            session.close()

    def delete_data(self, model, filters=None):
        """
        删除数据
        :param model: 数据库表对应的 SQLAlchemy 模型类
        :param filters: 查询条件，字典形式，键为字段名，值为字段值
        :return: 删除成功返回 True，失败返回 False
        """
        session = self.Session()
        try:
            query = session.query(model)
            if filters:
                for key, value in filters.items():
                    query = query.filter(getattr(model, key) == value)
            rows_affected = query.delete()
            session.commit()
            return rows_affected > 0
        except Exception as e:
            session.rollback()
            print(f"删除数据时出错: {e}")
            return False
        finally:
            session.close()

if __name__ == "__main__":
    # 数据库连接 URL，这里以 SQLite 为例
    db_url = 'mysql+pymysql://viu_root:myviu_4359@10.63.4.122:23306/test'
    db_manager = DatabaseManager(db_url)

    # 创建表
    # db_manager.create_tables()

    # 单条插入示例
    single_data = {
        "biz_id": 1,
        "biz_name": "Alice",
        "vmid": 123456,
        "flow_id": "Alice",
        "date": datetime(2024, 10, 15, 14, 30, 0)
    }
    db_manager.insert_data(FlowReport, single_data)


    # # 批量插入示例
    # batch_data = [
    #     {
    #         "name": "Bob",
    #         "age": 30
    #     },
    #     {
    #         "name": "Charlie",
    #         "age": 35
    #     }
    # ]
    # db_manager.insert_data(User, batch_data)
    #
    # # 查询示例
    # filters = {"name": "Alice"}
    # results = db_manager.query_data(User, filters)
    # for result in results:
    #     print(f"查询结果: ID={result.id}, 姓名={result.name}, 年龄={result.age}")
    #
    # # 修改示例
    # update_filters = {"name": "Bob"}
    # update_data = {"age": 31}
    # if db_manager.update_data(User, update_filters, update_data):
    #     print("数据修改成功")
    # else:
    #     print("数据修改失败")
    #
    # # 删除示例
    # delete_filters = {"name": "Charlie"}
    # if db_manager.delete_data(User, delete_filters):
    #     print("数据删除成功")
    # else:
    #     print("数据删除失败")


